﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;


namespace CashierSystem.CashierInManager
{
    public class CashierInManager:DbConnection
    {
        public static string loadOneCustomerName(string regNo)
        {
            string sql = "select customerName from t_customer where customerId = " + regNo ;
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            string customerName = "";
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    customerName = reader["customerName"].ToString();
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            return customerName;
        }

        public static void getDepartInfo(out string[] departName, out string[] departId)
        {
            ArrayList aldepartId = new ArrayList();
            ArrayList aldepartName = new ArrayList();
            aldepartId.Add("0");
            aldepartName.Add(" ");

            string sql = "select departId,departName from t_department";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbreader = cmd.ExecuteReader();
                while (dbreader.Read())
                {
                    aldepartId.Add(dbreader["departId"].ToString());
                    aldepartName.Add(dbreader["departName"].ToString());
                }
                conn.Close();

            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            departId = new string[aldepartId.Count];
            departName = new string[aldepartName.Count];
            for (int i = 0; i < departId.Length; i++)
            {
                departId[i] = (string)aldepartId[i];
                departName[i] = (string)aldepartName[i];
            }
        }

        public static void getItemInfo(out string[] itemName, out string[] itemId,out string[] price,string  departId)
        {
            ArrayList alItemId = new ArrayList();
            ArrayList alItemName = new ArrayList();
            ArrayList alPrice = new ArrayList();
            alItemId.Add("0");
            alItemName.Add(" ");
            alPrice.Add(" ");

            string sql = "select t_items.itemId,itemName,price from t_items inner join t_departItems on t_departItems.itemId = t_items.itemId where departId = '" + departId + "' ";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbreader = cmd.ExecuteReader();
                while (dbreader.Read())
                {
                    alItemId.Add(dbreader["itemId"].ToString());
                    alItemName.Add(dbreader["itemName"].ToString());
                    alPrice.Add(dbreader["price"].ToString());

                }
                conn.Close();

            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            itemId = new string[alItemId.Count];
            itemName = new string[alItemName.Count];
            price = new string[alPrice.Count];
            for (int i = 0; i < itemId.Length; i++)
            {
                itemId[i] = (string)alItemId[i];
                itemName[i] = (string)alItemName[i];
                price[i] = (string)alPrice[i];
            }
        }

        public static void saveCheckItems(string customerId, string itemId,string doctorId,string doctorId1,string doctorId2,string doctorId3,string doctorId4,string doctorId5,string expertFee)
        {
            string date = DateTime.Now.ToString();
            DateTime d = DateTime.Parse(date);
            string sd = d.ToString("yyyy-MM-dd");
            string sql = "insert into t_sensing(customerId,itemId,sensingDate,doctorId,doctorId1,doctorId2,doctorId3,doctorId4,doctorId5,expertFee) values('";
            sql += customerId + "','" + itemId + "','";
            sql += sd + "','" + doctorId + "','" + doctorId1 + "','" + doctorId2 + "','" + doctorId3 + "','" + doctorId4 + "','";
            sql += doctorId5 + "','" + expertFee + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static void savePay(string payMethod,string actualPay,string discount,string sensingId,string cashierId,string cardId)
        {
            string date = DateTime.Now.ToString();
            DateTime d = DateTime.Parse(date);
            string sd = d.ToString("yyyy-MM-dd");
            string sql = "insert into t_pay(payMethod,actualPay,discount,payDate,sensingId,cashierId,cardId) ";
            sql += "values('" + payMethod + "','" + actualPay + "','" + discount + "','" + sd +"','" + sensingId + "','" + cashierId + "','" + cardId +"') ";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static string getMaxSensingId()
        {
            string sql = "SELECT MAX(sensingId) AS Expr1 FROM t_sensing";
            string maxSensingId = "";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    maxSensingId = reader["Expr1"].ToString();
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            return maxSensingId;
 
        }

        public static void deleteSensingItem(string sensingId)
        {
            string sql = "delete from t_sensing where sensingId = '" + sensingId + "'";
            try
            {
                conn.Close();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static void getDoctorInfo(out string[] doctorName, out string[] doctorId,string itemId)
        {
            ArrayList alDoctorId = new ArrayList();
            ArrayList alDoctorName = new ArrayList();
            alDoctorId.Add("0");
            alDoctorName.Add("请选择");
            string sql = "SELECT t_user.userId, t_user.name FROM t_items INNER JOIN t_doctorItems ON t_doctorItems.itemId = t_items.itemId ";
            sql += "LEFT OUTER JOIN t_user ON t_doctorItems.userId = t_user.userId INNER JOIN t_userRole ON t_userRole.userId = t_user.userId where t_items.itemId = '";
            sql += itemId + "' and t_userRole.roleId = '2'";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbreader = cmd.ExecuteReader();
                while (dbreader.Read())
                {
                    alDoctorId.Add(dbreader["userId"].ToString());
                    alDoctorName.Add(dbreader["name"].ToString());

                }
                conn.Close();

            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            doctorId = new string[alDoctorId.Count];
            doctorName = new string[alDoctorName.Count];
            for (int i = 0; i < doctorId.Length; i++)
            {
                doctorId[i] = (string)alDoctorId[i];
                doctorName[i] = (string)alDoctorName[i];
            }


 
        }

        public static void getTiaoLiDoctorInfo(out string[] doctorName, out string[] doctorId, string itemId)
        {
            ArrayList alDoctorId = new ArrayList();
            ArrayList alDoctorName = new ArrayList();
            alDoctorId.Add("0");
            alDoctorName.Add("请选择");
            string sql = "SELECT t_user.userId, t_user.name FROM t_items INNER JOIN t_doctorItems ON t_doctorItems.itemId = t_items.itemId ";
            sql += "LEFT OUTER JOIN t_user ON t_doctorItems.userId = t_user.userId INNER JOIN t_userRole ON t_userRole.userId = t_user.userId where t_items.itemId = '";
            sql += itemId + "' and t_userRole.roleId = '5'";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbreader = cmd.ExecuteReader();
                while (dbreader.Read())
                {
                    alDoctorId.Add(dbreader["userId"].ToString());
                    alDoctorName.Add(dbreader["name"].ToString());

                }
                conn.Close();

            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            doctorId = new string[alDoctorId.Count];
            doctorName = new string[alDoctorName.Count];
            for (int i = 0; i < doctorId.Length; i++)
            {
                doctorId[i] = (string)alDoctorId[i];
                doctorName[i] = (string)alDoctorName[i];
            }



        }

        public static void getExpertDoctorInfo(out string[] doctorName, out string[] doctorId, string itemId)
        {
            ArrayList alDoctorId = new ArrayList();
            ArrayList alDoctorName = new ArrayList();
            alDoctorId.Add("0");
            alDoctorName.Add("请选择");
            string sql = "SELECT t_user.userId, t_user.name FROM t_items INNER JOIN t_doctorItems ON t_doctorItems.itemId = t_items.itemId ";
            sql += "LEFT OUTER JOIN t_user ON t_doctorItems.userId = t_user.userId INNER JOIN t_userRole ON t_userRole.userId = t_user.userId where t_items.itemId = '";
            sql += itemId + "' and t_userRole.roleId = '4'";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbreader = cmd.ExecuteReader();
                while (dbreader.Read())
                {
                    alDoctorId.Add(dbreader["userId"].ToString());
                    alDoctorName.Add(dbreader["name"].ToString());

                }
                conn.Close();

            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            doctorId = new string[alDoctorId.Count];
            doctorName = new string[alDoctorName.Count];
            for (int i = 0; i < doctorId.Length; i++)
            {
                doctorId[i] = (string)alDoctorId[i];
                doctorName[i] = (string)alDoctorName[i];
            }



        }

        public static void saveSaleCard(string cardId,string customerId,string cardType,string price,string times,string actualPrice,string endDate)
        {
            string date = DateTime.Now.ToString();
            DateTime d = DateTime.Parse(date);
            string sd = d.ToString("yyyy-MM-dd");
            DateTime availableDate = DateTime.Parse(endDate);
            string ab = availableDate.ToString("yyyy-MM-dd");
            string sql = "insert into t_saleCard(cardId,customerId,cardType,price,times,actualPrice,saleDate,remainMoney,remainTime,availableDate) values('" + cardId + "','" + customerId + "','" + cardType + "','";
            sql +=  price + "','" + times + "','" + actualPrice + "','" + sd + "','" + price + "','" + times + "','" + ab + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static void searchSaleCard(ListView lv,string startDate,string endDate,string cardType)
        {
            string sql = "SELECT t_customer.customerName, t_saleCard.cardId, t_saleCard.cardType,t_saleCard.price, t_saleCard.times, t_saleCard.actualPrice, t_saleCard.saleDate,"; 
            sql += "t_saleCard.remainMoney, t_saleCard.remainTime, t_saleCard.availableDate FROM t_saleCard INNER JOIN t_customer ON t_customer.customerId = t_saleCard.customerId ";
            if (!startDate.Equals(""))
            {
                sql += " and saleDate >= '" + startDate + "'";//convert(VARCHAR(10),
            }
            if (!endDate.Equals(""))
            {
                sql += " and saleDate <= '" + endDate + "'";
            }
            if (!cardType.Equals(""))
            {
                sql += " and cardType = '" + cardType + "'";
            }
            lv.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    ListViewItem li = new ListViewItem();
                    li.SubItems.Clear();
                    li.SubItems[0].Text = dbReader["cardId"].ToString();
                    li.SubItems.Add(dbReader["customerName"].ToString());
                    li.SubItems.Add(dbReader["cardType"].ToString());
                    li.SubItems.Add(dbReader["price"].ToString() + "元");
                    li.SubItems.Add(dbReader["actualPrice"].ToString() + "元");
                    li.SubItems.Add(dbReader["times"].ToString());
                    DateTime d = DateTime.Parse(dbReader["saleDate"].ToString());
                    string sd = d.ToString("yyyy-MM-dd");
                    li.SubItems.Add(sd);
                    li.SubItems.Add(dbReader["remainMoney"].ToString());
                    li.SubItems.Add(dbReader["remainTime"].ToString());
                    DateTime x = DateTime.Parse(dbReader["availableDate"].ToString());
                    string y = x.ToString("yyyy-MM-dd");
                    li.SubItems.Add(y);
                    lv.Items.Add(li);
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }

        }

    }
}
